package edu.fzu.fundmgr.dao.impl;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.fzu.fundmgr.dao.ClientDao;
import edu.fzu.fundmgr.domain.Client;
import edu.fzu.fundmgr.utils.DBUtils;

public class ClientDaoJDBCImpl implements ClientDao {

	private static final String SQL_ADD = "insert into client(idcard_no,client_name,sex,phone,address,email,hobby,created_date) values(?,?,?,?,?,?,?,?)";
	private static final String SQL_LOADALL = "select * from client order by idcard_no desc";
	private static final String SQL_DEL = "delete from client where idcard_no=?";
	private static final String SQL_GETBYID = "select * from client where idcard_no=?";
	private static final String SQL_UPDATE = "update client set idcard_no=?,client_name=?,sex,phone=?,address=?,email=?,hobby=?,created_date=?";

	public void addClient(Client client) {

		DBUtils utils = new DBUtils();
		Connection conn = null;
		PreparedStatement pstmt = null;

		conn = utils.getConn();

		try {

			pstmt = conn.prepareStatement(SQL_ADD);

			pstmt.setString(1, client.getIdCardNO());
			pstmt.setString(2, client.getClientName());
			pstmt.setString(3, client.getSex());
			pstmt.setString(4, client.getPhone());
			pstmt.setString(5, client.getAddress());
			pstmt.setString(6, client.getEMail());
			pstmt.setString(7, client.getHobby());
			pstmt.setTimestamp(8, new java.sql.Timestamp(client.getCreatedDate()
					.getTime()));
			pstmt.executeUpdate();
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			utils.releaseResource(conn, pstmt, null);
		}
	}

	public List<Client> loadAll() {

		DBUtils utils = new DBUtils();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<Client> clientList = null;

		conn = utils.getConn();

		try {

			pstmt = conn.prepareStatement(SQL_LOADALL);
			rset = pstmt.executeQuery();
			clientList = new ArrayList<Client>();

			while (rset.next()) {

				Client client = new Client();
				client.setIdCardNO(rset.getString("idcard_no"));
				client.setClientName(rset.getString("client_name"));
				client.setSex(rset.getString("sex"));
				client.setPhone(rset.getString("phone"));
				client.setAddress(rset.getString("address"));
				client.setEMail(rset.getString("email"));
				client.setHobby(rset.getString("hobby"));
				client.setCreatedDate(rset.getTime("created_date"));

				clientList.add(client);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			utils.releaseResource(conn, pstmt, rset);
		}

		return clientList;
	}

	public void delClient(String idCardNO) {

		DBUtils utils = new DBUtils();
		Connection conn = null;
		PreparedStatement pstmt = null;

		conn = utils.getConn();

		try {

			pstmt = conn.prepareStatement(SQL_DEL);
			pstmt.setString(1, idCardNO);
			pstmt.executeUpdate();
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			utils.releaseResource(conn, pstmt, null);
		}
	}

	public Client getClient(String idCardNO) {

		DBUtils utils = new DBUtils();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Client client = null;

		conn = utils.getConn();

		try {

			pstmt = conn.prepareStatement(SQL_GETBYID);
			pstmt.setString(1, idCardNO);
			rset = pstmt.executeQuery();

			if (rset.next()) {

				client = new Client();

				client.setIdCardNO(rset.getString("idcard_no"));
				client.setClientName(rset.getString("client_name"));
				client.setSex(rset.getString("sex"));
				client.setPhone(rset.getString("phone"));
				client.setAddress(rset.getString("address"));
				client.setEMail(rset.getString("email"));
				client.setHobby(rset.getString("hobby"));
				client.setCreatedDate(rset.getTime("created_date"));
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			utils.releaseResource(conn, pstmt, rset);
		}

		return client;
	}

	public void updateClient(Client client) {

		DBUtils utils = new DBUtils();
		Connection conn = null;
		PreparedStatement pstmt = null;

		conn = utils.getConn();

		try {

			pstmt = conn.prepareStatement(SQL_UPDATE);

			pstmt.setString(1, client.getIdCardNO());
			pstmt.setString(2, client.getClientName());
			pstmt.setString(3, client.getSex());
			pstmt.setString(4, client.getPhone());
			pstmt.setString(5, client.getAddress());
			pstmt.setString(6, client.getEMail());
			pstmt.setString(7, client.getHobby());
			pstmt.setTimestamp(8, new java.sql.Timestamp(client.getCreatedDate()
					.getTime()));

			pstmt.executeUpdate();
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			utils.releaseResource(conn, pstmt, null);
		}
	}

}
